package dbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import vo.Student;

public class DBOperator {
    private Connection conn = null;
    private static final String DBDRIVER = "com.mysql.jdbc.Driver";
    private static final String DBURL = "jdbc:mysql://localhost:3306/web_12?charactorEncoding=gbk";
    private static final String DBUSER = "root";
    private static final String DBPASSWORD = "123456";
    
    public Connection getConnection() {
        
        try {
            // 加载驱动
            Class.forName(DBDRIVER);
            // 获取连接
            conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return conn;
    }
    
    public void close() {
        while (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            conn = null;
        }
    }
    
    public boolean doUpdate() {
        boolean flag = false;
        // 连接数据库
        getConnection();
        
        // 关闭数据库
        close();
        return flag;
    }
    
    public boolean doDelete() {
        boolean flag = false;
        // 连接数据库
        getConnection();
        
        // 关闭数据库
        close();
        return flag;
    }
    
    public boolean doInsert(Student stu) {
        boolean flag = false;
        // 连接数据库
        getConnection();
        PreparedStatement pstmt = null;
        
        
        try {
            String sql = "INSERT INTO STUDENT values(?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, stu.getSid());
            pstmt.setString(2, stu.getSname());
            pstmt.setString(3, stu.getSex());
            pstmt.setInt(4, stu.getAge());
            pstmt.setString(5, stu.getSclass());

            if (pstmt.executeUpdate() > 0) {
                flag = true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        // 关闭数据库
        close();
        return flag;
    }
    
    public List<Student> doQuery() {
        List<Student> stus = new ArrayList<Student>();
        // 连接数据库
        getConnection();
        try {
            Statement stmt = conn.createStatement();
            String sql = "select * from Student";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Student stu = new Student();
                stu.setSid(rs.getString(1));
                stu.setSname(rs.getString(2));
                stu.setSex(rs.getString(3));
                stu.setAge(rs.getInt(4));
                stu.setSclass(rs.getString(5));
                stus.add(stu);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        // 关闭数据库
        close();
        return stus;
    }
    
    public List<Student> doSelectById(String sid) {
        // 连接数据库
        getConnection();
        List<Student> stus = new ArrayList<Student>();
        try {
            Statement stmt = conn.createStatement();
            String sql = "select * from Student where sid like '%"+sid+"%'";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Student stu = new Student();
                stu.setSid(rs.getString(1));
                stu.setSname(rs.getString(2));
                stu.setSex(rs.getString(3));
                stu.setAge(rs.getInt(4));
                stu.setSclass(rs.getString(5));
                stus.add(stu);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        // 关闭数据库
        close();
        return stus;
    }
}
